﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class KhachHangDAO
    {
        public List<KhachHangDTO> LayKhachHang()
        {
            string sql = "SELECT * FROM KhachHang WHERE Deleted=0 ORDER BY SoTT";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                List<KhachHangDTO> listkh = new List<KhachHangDTO>();
                while (read.Read())
                {
                    KhachHangDTO kh = new KhachHangDTO();
                    kh.MaKH = read.GetString(0);
                    kh.TenKH = read.GetString(1);
                    kh.MaVaTen = kh.MaKH + " - " + kh.TenKH;
                    kh.TenCongTy = read.GetString(2);
                    kh.MaSoThue = read.GetString(3);
                    kh.DiaChi = read.GetString(4);
                    kh.DienThoai = read.GetString(5);
                    kh.Fax = read.GetString(6);
                    kh.Email = read.GetString(7);
                    listkh.Add(kh);
                }
                conn.Close();
                return listkh;
            }
        }
        public KhachHangDTO LayKhachTheoMa(string MaKH)
        {
            string sql = "SELECT * FROM KhachHang WHERE MaKH = '" + MaKH + "'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                read.Read();
                KhachHangDTO kh = new KhachHangDTO();
                kh.MaKH = read.GetString(0);
                kh.TenKH = read.GetString(1);
                kh.MaVaTen = kh.MaKH + " - " + kh.TenKH;
                kh.TenCongTy = read.GetString(2);
                kh.MaSoThue = read.GetString(3);
                kh.DiaChi = read.GetString(4);
                kh.DienThoai = read.GetString(5);
                kh.Fax = read.GetString(6);
                kh.Email = read.GetString(7);
                conn.Close();
                return kh;
            }
        }
        public bool KTMaKH(string MaKH)
        {
            string sql = "SELECT COUNT(*) FROM KhachHang WHERE MaKH='" + MaKH + "'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                read.Read();
                int kq = read.GetInt32(0);
                conn.Close();
                if (kq == 0)
                    return true;
                return false;
            }

        }
        public bool ThemKhachHang(KhachHangDTO kh)
        {
            string sql = "INSERT INTO KhachHang(MaKH, TenKH, TenCongTy, MaSoThue, DiaChi, DienThoai, Fax, Email, Deleted)";
            sql += "VALUES('" + kh.MaKH + "',N'" + kh.TenKH + "', N'" + kh.TenCongTy + "', '" + kh.MaSoThue + "',N'" + kh.DiaChi + "', '" + kh.DienThoai + "', '" + kh.Fax + "', '" + kh.Email + "', 0)";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                int kq = cmd.ExecuteNonQuery();
                conn.Close();
                if (kq >= 0)
                    return true;
                return false;
            }
        }
        public bool SuaThongTinKhachHang(KhachHangDTO kh)
        {
            string sql = "UPDATE KhachHang SET TenKH = N'" + kh.TenKH+ "', ";
            sql += "TenCongTy = N'" + kh.TenCongTy + "', MaSoThue = '" + kh.MaSoThue + "',DiaChi = '" + kh.DiaChi + "',DienThoai = '" + kh.DienThoai + "',Fax = '" + kh.Fax + "',Email = '" + kh.Email + "' WHERE MaKH = '" + kh.MaKH + "'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                int kq = cmd.ExecuteNonQuery();
                conn.Close();
                if (kq >= 0)
                    return true;
                return false;
            }
        }


        public List<KhachHangDTO> LayKhachHangTheoTen(string TenKH)
        {
            string sql = "SELECT * FROM KhachHang WHERE TenKH like N'%" + TenKH + "%'"; //= N'%" + TenKH + "%'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                List<KhachHangDTO> danhsach = new List<KhachHangDTO>();
                while (read.Read())
                {
                    KhachHangDTO khDTO = new KhachHangDTO();
                    khDTO.MaKH = read.GetString(0);
                    khDTO.TenKH = read.GetString(1);
                    khDTO.TenCongTy = read.GetString(2);
                    khDTO.MaSoThue = read.GetString(3);
                    khDTO.DiaChi = read.GetString(4);
                    khDTO.DienThoai = read.GetString(5);
                    khDTO.Fax = read.GetString(6);
                    khDTO.Email = read.GetString(7);
                    danhsach.Add(khDTO);
                }
                conn.Close();
                return danhsach;
            }
        }


        public bool XoaKhachHang(string MaKH)
        {
            string sql = "UPDATE KhachHang SET Deleted = 1 WHERE MaKH = N'" + MaKH + "'";
            using (SqlConnection conn = DataProvider.ConnectDB())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                int kq = cmd.ExecuteNonQuery();
                conn.Close();
                if (kq >= 0)
                    return true;
                return false;
            }
        }

    }
}
